from flask import Flask, render_template, url_for
import json
from sql import get_data, get_all_data
app = Flask(__name__)


@app.route('/')
def hello_world():
    return render_template('echarts.html')


@app.route('/data_global_confirm_top11')
def data_global_confirm_top11():
    sql = '''
    select countryName,max(province_dead) as max_dead from covid 
    where MONTH(updateTime)='11'
    group by countryName 
    order by max_dead desc
    limit 50
    '''
    res = json.loads(get_data(sql))
    data = []
    data_list = {}
    name = res['name']
    # 重新组装数据
    for i in range(len(res['name'])):
        data_map = {}
        data_map['name'] = res['name'][i]
        data_map['value'] = res['count'][i]
        data.append(data_map)
    data_list['name'] = name
    data_list['count'] = data
    return json.dumps(data_list, ensure_ascii=False)


# 返回11月份湖北省确诊、感染、治愈、死亡人数：折线图


@app.route('/data_hubei')
def data_huibei():
    sql = '''
    select SUBSTRING(updatetime,6,5) as
    month_day,max(province_confirm),max(province_suspect),max(province_cured),max(province_dead)
    FROM
    covid
    WHERE
    covid.countryName = '中国' AND
    covid.provinceName != '中国' AND
    month(updateTime) = '11' AND
    covid.provinceName = '湖北省'
    group by month_day
    order by month_day

    '''
    return get_all_data(sql)

    # 返回2-11月美国的全部数据：折线图


@app.route('/data_usa')
def data_usa():
    sql = '''
    select MONTH(updateTime) as
    `month`,max(province_confirm),max(province_suspect),max(province_cured),max(province_dead) from covid
    where countryName='美国' and MONTH(updateTime) BETWEEN 2 and 11
    group by MONTH(updatetime)
    order by `month`
 '''
    return get_all_data(sql)


@app.route('/data_province_confirmed_max')
def data_global_confirm():
    sql = '''SELECT
covid.countryName AS `name`,
Count(*) AS count
FROM
covid
GROUP BY
covid.countryName
ORDER BY count
deSC
limit 10
'''

    return get_data(sql)


@app.route('/data_province_confirmed_min')
def data_global_confirm1():
    sql = '''SELECT
covid.countryName AS `name`,
Count(*) AS count
FROM
covid
GROUP BY
covid.countryName
ORDER BY count
deSC
limit 10
'''
    res = json.loads(get_data(sql))
    data = []
    data_list = {}
    name = res['name']
    for i in range(len(res['name'])):
        data_map = {}
        data_map['name'] = res['name'][i]
        data_map['value'] = res['count'][i]
        data.append(data_map)
    data_list['name'] = name
    data_list['count'] = data
    return json.dumps(data_list, ensure_ascii=False)
# 返回11月份国内确诊人数，以省为单位：中国地图


@app.route('/data_province_confirm')
def data_province_confirm():
    sql = '''
    select provinceName,max(province_confirm) as max_confirm from covid
    where countryName='中国' and provinceName!='中国' and month(updateTime)='11'
    group by provinceName
    '''
    res = json.loads(get_data(sql))
    data = []
    for i in range(len(res['name'])):
        data_map = {}
        # 将省份名后的省、市字移除
        province = ((res['name'][i]).replace('省', '')).replace('市', '')
        data_map['name'] = province
        data_map['value'] = res['count'][i]
        data.append(data_map)
    return json.dumps(data, ensure_ascii=False)


@app.route('/data_global_confirm_top10')
def data_global_confirm_top10():
    sql = '''
    select countryName,max(province_dead) as max_dead from covid
    where MONTH(updateTime)='11'
    group by countryName
    order by max_dead desc
    limit 50
    '''
    res = json.loads(get_data(sql))
    data = []
    data_list = {}
    name = res['name']
    for i in range(len(res['name'])):
        data_map = {}
        data_map['name'] = res['name'][i]
        data_map['value'] = res['count'][i]
        data.append(data_map)
    data_list['name'] = name
    data_list['count'] = data
    return json.dumps(data_list, ensure_ascii=False)


if __name__ == '__main__':
    app.run(debug=True)
